package dbconnection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;


import loja.Armazem;
import loja.JaExisteException;


/**
 * @author IME 2010
 * 
 * Class used to establish a connection with a defined DataBase
 */

public class BDarmazem {

	private BaseDados base_dados = null;


	public BDarmazem(BaseDados bd){
		this.base_dados=bd;
	}

	/***********************************************************
	 * Inserts the Armazem a into the DataBase
	 * @param a Armazem to be inserted in the DataBase
	 * @throws JaExisteException if the Armazem a already exists in the DataBase
	 * @return true if the Armazem a was successfully inserted into the DataBase; false otherwise
	 ***********************************************************/
	protected boolean adiciona(Armazem a) {
		boolean sucesso = false;
		if(a!=null){
			String tablename="armazem";
			String query = "insert INTO `" + tablename +"` (`nome`, `descricao`) VALUES ('"+a.getNome()+"', '"+a.getDescricao()+"');" ;
			try {
				this.base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();	
			}
		}
		
		return sucesso;
	}

	/**
	 * Updates the Armazem armazem in the DataBase to match the given fields
	 * @param armazem Armazem to be updated in the DataBase
	 * @param nome	new name for the Armazem armazem
	 * @param descricao new descricao for the Armazem armazem
	 * @return true if the Armazem armazem was successfully updated; false otherwise
	 */
	protected boolean alteraArmazem(Armazem armazem, String nome, String descricao){
		boolean sucesso = false;
		if(nome!=null&&descricao!= null){
			String tabela = "armazem";
			String query = "UPDATE "+tabela+" SET `nome`='"+nome+"', `descricao`='"+descricao+"' WHERE `idarmazem`='"+armazem.getId_armazem()+"';";

			try {
				base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();
				sucesso=false;
			}
		}
		return sucesso;
	}


	/***********************************************************
	 * Indicates whether the Armazem a exists in the DataBase
	 * @param a the Armazem to be searched in the Database
	 * @return true if the Armazem a exists in the DataBase; false otherwise
	 ***********************************************************/
	protected boolean existe(Armazem a){
		if(a!=null){
			try{
				return existe(a.getId_armazem());
			}catch(java.lang.NumberFormatException e){
				return false;
			}
		}
		return false;
	}

	/***********************************************************
	 * Removes the Armazem a from the DataBase
	 * @param a the Armazem to be removed from the Database
	 * @return true if the Armazem a was correctly removed from the DataBase; false otherwise
	 ***********************************************************/
	protected boolean retira(Armazem a)
	{
		boolean sucesso = false;
		if(a!=null){
			if(existe(a)){

				String tablename="quebra";
				String query = "DELETE FROM " + tablename +" WHERE `stock_armazem_idarmazem` = '"+a.getId_armazem()+"' ;";
				String tablename2="stock";
				String query2 = "DELETE FROM " + tablename2 +" WHERE `armazem_idarmazem` = '"+a.getId_armazem()+"' ;";
				String tablename3="armazem";
				String query3 = "DELETE FROM " + tablename3 +" WHERE `idarmazem` = '"+a.getId_armazem()+"' ;";

				try {
					base_dados.executeStatement(query);
					base_dados.executeStatement(query2);
					base_dados.executeStatement(query3);
					sucesso= true;
				} catch (SQLException e) {
					e.printStackTrace();
					sucesso = false;
				}
			}
		}
		return sucesso;
	}

	/***********************************************************
	 * Returns an Armazem from the DataBase using the id argument as a reference
	 * @param id the integer which will be used to search an Armazem by its id in the DataBase
	 * @return the Armazem in the DataBase whose id is equal to the id argument; null if the id argument is not an id in the Table Armazem in the DataBase;
	 ***********************************************************/
	protected Armazem getArmazem(int id) {

		if(existe(id)){
			Armazem a = null;
			String tablename="armazem";
			String query = "SELECT * FROM  "+tablename+"  WHERE `idarmazem` = '"+id+"' ;";
			try {
				ResultSet rs=base_dados.executeStatementQuery(query);
				rs.next();
				a = new Armazem(rs.getInt(1),rs.getString(2),rs.getString(3));
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return a;
		}
		return null;
	}

	/***********************************************************
	 * Returns all Armazens present in the DataBase
	 * @return the LinkedList<Armazem> which contains all Armazens in the DataBase; null if the DataBase has no Armazens;
	 ***********************************************************/
	protected LinkedList<Armazem> getArmazens(){

		if(naoVazia()){		
			LinkedList<Armazem> lista_armazens = null;
			String tabela="armazem";
			String query = "SELECT * FROM " + tabela +";";
			try {
				ResultSet rs=base_dados.executeStatementQuery(query);
				lista_armazens = getArmazens(rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}

			return lista_armazens;
		}
		return null;
	}

	private LinkedList<Armazem> getArmazens(ResultSet rs) throws SQLException {
		LinkedList<Armazem> lista_armazens= new LinkedList<Armazem>();
		while (rs.next()){
//			lista_armazens.add(getArmazem(rs.getInt(1)));
//			rs.next();
			lista_armazens.add(new Armazem(rs.getInt(1),rs.getString(2),rs.getString(3)));
		}

		return lista_armazens;
	}

	private boolean naoVazia() {
		String tabela = "armazem";
		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" + tabela +";";
		try {
			ResultSet rs=base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;

		} catch (SQLException e) {
			e.printStackTrace();
			existe = false;
		}

		return existe;
	}

	/***********************************************************
	 * Indicates whether an object with the given id exists under a table (given as an argument) in the DataBase
	 * @param id the reference id with which to search a table in the DataBase
	 * @param tablename the name of the table to search in the DataBase 
	 * @return true if the Object referenced by the id argument exists in the DataBase under the table with the name tablename argument
	 ***********************************************************/
	private boolean existe(int id) {
		String tablename = "armazem";
		String column_name = "idarmazem";

		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" +tablename +"` WHERE `"+column_name+"` = '"+id+"' ;";
		try {
			ResultSet rs=base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return existe;
	}
}
